# -*- coding:utf-8 -*-
"""
@file: mysql_demo.py 
@time: 2019/07/18
@author: 
descript:
"""
import pymysql

#在数据库中插入数据
def insertData(db,cursor):
	# 插入数据
	sql = """insert into student (id,name,age) values (1,'小明',18),(2,'小兰',18);"""
	try:
		# 执行sql语句
		cursor.execute(sql)
		# 提交到数据库执行
		db.commit()
		
		print("successfully insert data")
	except:
		# 发生错误时回滚
		db.rollback()

# 创建mysql表
def ceartTable(cursor):
	# cursor.execute("drop database if exists students")
	# cursor.execute("create database students")
	# cursor.execute("use students")
	sql = """cteate table if not exists student (
	'id' bigint,
	'name' varchar(20),
	'age' int default 1
	)"""
	cursor.execute("drop table if exists student")
	cursor.execute(sql)
	
	print("successfully create table")

# 显示
def readTable(cursor):
	cursor.execute("select * from student")
	results = cursor.fetchall
	
	for row in results:
		id = row[0]
		name = row[1]
		age = row[2]
		print("id =",id,"name =","age =",age,"\n")
	
	print("successfully show table")
	
# 查找
def findResord(cursor,key,value):
	sql = "select * from student where" + key + "=" + value
	cursor.excute(sql)
	results = cursor.fetchall
	
	print(results,"successfully find")
	
# 删除
def deleteRecord(db, cursor, key, value):
	sql = "delete from student where" + key + "=" + value
	cursor.execute(sql)
	db.commit()
	
	print("successfully detele")
	
if __name__ == '__main__':
	# 链接mysql数据库
	db = pymysql.connect(host='localhost', port=3306, user='root', passwd='root', db='yxl_test',
						 charset='utf8')
	# 创建指针
	cursor = db.cursor()
# 创建数据库和表
	ceartTable(cursor)
# 插入数据
	insertData(db,cursor)
	# 显示表格
	readTable(cursor)
	
	findResord(cursor,"name", "'小明'")
	# 删除
	deleteRecord(db, cursor, "name", "'小明'")
	
	# 更改
	sql = "update student set age=20 where id=2"
	cursor.execute(sql)
	db.commit()
	
	readTable(cursor)
	
	# 关闭游标链接
	cursor.close()
	# 关闭数据库服务器连接，释放内存
	db.close()

	
